﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Profari_Heldur_Utan_Um_Prof
{
    public partial class SessionView : Form
    {
        private static SqlConnection con = new SqlConnection("Data Source=orninn.com;Initial Catalog=Notendaprofanir;Persist Security Info=True;User ID=SonjaNew;Password=Léttjógúrt");
        private static DataTable dt = new DataTable();
        private static SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM UserTestSession", con);
        private static DataSet ds = new DataSet();
        SqlDataReader r = null;
        public static List<BloodPressure> bloodPressure = new List<BloodPressure>();
        int userID;

        int globalGroupID;
        int globalEvolutionID;

        public struct BloodPressure
        {
            public int diastolic;
            public int systolic;
            public int pressure;
        }
       

        public SessionView()
        {
            this.StartPosition = FormStartPosition.CenterScreen;
            InitializeComponent();
            globalGroupID = ListOfProjects.globalGroupID;
            globalEvolutionID = ListOfProjects.globalEvaluationID;
            
        }

        private void SessionView_Load(object sender, EventArgs e)
        {            
            fillTaskListBox();
            fillEvolutionComboBox();
            fillDataGridView();
            createLineGraph();
            evaluationBox.SelectedIndex = evaluationBox.FindStringExact(getEvalName());
            groupName.Text = getGroupsName();
            
            dataGridViewTask.Hide();
        }

        #region Functions
        public void createLineGraph()
        {
            chart1.ChartAreas.Add("area");
            chart1.ChartAreas["area"].AxisX.Minimum = 0;
            chart1.ChartAreas["area"].AxisX.Maximum = 10;
            chart1.ChartAreas["area"].AxisX.Interval = 1;
            chart1.ChartAreas["area"].AxisX.MajorGrid.Enabled = false;
            chart1.ChartAreas["area"].AxisY.Minimum = 0;
            chart1.ChartAreas["area"].AxisY.Interval = 50;
            chart1.ChartAreas["area"].AxisY.MajorGrid.Enabled = false;


           /* chart1.Series.Add("User 1");

            chart1.Series["User 1"].Color = Color.Green;

            chart1.Series["User 1"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;

            chart1.Series["User 1"].Points.AddXY(0, 0);

            for (int i = 1; i < (dataGridViewTask.RowCount - 1); i++)
            {
                //MessageBox.Show("Row count: " + this.dataGridViewTask[1, 1].Value);
                chart1.Series["User 1"].Points.AddXY(i, dataGridViewTask[1, i + 1].Value);
            }*/
        }

        public void insertUsersGraph()
        {
            chart1.Series.Clear();

            chart1.Series.Add("Diastolic");
            chart1.Series["Diastolic"].Color = Color.Green;
            chart1.Series["Diastolic"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
            chart1.Series["Diastolic"].Points.AddXY(0, 0);

            chart1.Series.Add("Systolic");
            chart1.Series["Systolic"].Color = Color.Blue;
            chart1.Series["Systolic"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
            chart1.Series["Systolic"].Points.AddXY(0, 0);

            chart1.Series.Add("Pressure");
            chart1.Series["Pressure"].Color = Color.Orange;
            chart1.Series["Pressure"].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
            chart1.Series["Pressure"].Points.AddXY(0, 0);

            int i = 1;
            foreach(BloodPressure bp in bloodPressure)
            {
                chart1.Series["Diastolic"].Points.AddXY(i, bp.diastolic);
                chart1.Series["Systolic"].Points.AddXY(i, bp.systolic);
                chart1.Series["Pressure"].Points.AddXY(i, bp.pressure);
                //chart1.Series["User 1"].Points.AddXY(i, dataGridViewTask[1, i + 1].Value);
                i++;
            }
        }

        private void fillDataGridView()
        {
/*            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\HlynurX\Documents\Lokaverkefni\Lokaverkefni_Notendaprofanir\Profari_Heldur_Utan_Um_Prof\Prufa_Lokaverkefnib.xlsx" + @";Extended Properties =""Excel 8.0; HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

            OleDbCommand command = new OleDbCommand("SELECT Task, BloodPressure " + "FROM [Sheet1$]", conn);
            DataSet dsTasks = new DataSet();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dsTasks);

            dataGridViewTask.DataSource = dsTasks.Tables[0];*/
        }

        private void playTaskVideo()
        {
            string path = getVideoPath();
            axWindowsMediaPlayer1.URL = path;
        }
        #endregion

        #region Buttons
        private void Cancel_Click(object sender, EventArgs e)
        {
            DialogResult cancelProject = MessageBox.Show("Are you sure you want to stop viewing old projects?", "Quit", MessageBoxButtons.YesNo);
            if (cancelProject == DialogResult.Yes)
            {
                System.Threading.Thread testerInterface = new System.Threading.Thread(new System.Threading.ThreadStart(OpenTesterInterface));
                testerInterface.SetApartmentState(System.Threading.ApartmentState.STA);
                testerInterface.Start();
                this.Close();
            }
        }

        private void Back_Click(object sender, EventArgs e)
        {
            System.Threading.Thread listOfProjects = new System.Threading.Thread(new System.Threading.ThreadStart(OpenListOfProjects));
            listOfProjects.SetApartmentState(System.Threading.ApartmentState.STA);
            listOfProjects.Start();
            this.Close();
        }

        private void btnFinish_Click(object sender, EventArgs e)
        {
            System.Threading.Thread testerInterface = new System.Threading.Thread(new System.Threading.ThreadStart(OpenTesterInterface));
            testerInterface.SetApartmentState(System.Threading.ApartmentState.STA);
            testerInterface.Start();
            this.Close();
        }
        #endregion

        #region Events
        private void taskList_SelectedIndexChanged(object sender, EventArgs e)
        {
            userInfo.Clear();
            participantsList.Items.Clear();
            fillParticipantList();
        }

        private void sessionBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            userInfo.Clear();
            participantsList.Items.Clear();
            taskList.Items.Clear();
            fillTaskListBox();

        }

        private void participantsList_SelectedIndexChanged(object sender, EventArgs e)
        {
            userInfo.Clear();
            bloodPressure.Clear();
            fillUserInfo();
            getBloodPressure();
            insertUsersGraph();
            playTaskVideo();
        }
        #endregion

        #region SQL Commands

        private int getTaskID()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT id FROM Task WHERE name ='"+ (string)taskList.SelectedItem + "'", con);
                r = cmd.ExecuteReader();

                if (r.Read())
                {
                    return r.GetInt32(0);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return 0;
        }

        private int getEvaluateID()
        {
            try
            {
                con.Open();
                int sessionID = 0;
                SqlCommand cmd = new SqlCommand("SELECT Evaluation.id FROM Evaluation INNER JOIN GroupEvaluations ON Evaluation.id = GroupEvaluations.evaluationID AND GroupEvaluations.groupsID='" + globalGroupID + "' AND Evaluation.name ='" + (string)evaluationBox.SelectedItem + "';", con);
                r = cmd.ExecuteReader();

                while (r.Read())
                {
                    return sessionID = r.GetInt32(0);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return 0;
        }

        private string getEvalName()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT name FROM Evaluation where id="+globalEvolutionID, con);
                r = cmd.ExecuteReader();

                while (r.Read())
                {

                    return r.GetValue(0).ToString();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;
        }

        private string getEvaluationName()
        {
            try
            {
                con.Open();
                string evaluationName = null;
                SqlCommand cmd = new SqlCommand("SELECT Evaluation.name FROM Evaluation INNER JOIN GroupEvaluations ON Evaluation.id = GroupEvaluations.evaluationID AND GroupEvaluations.groupsID=" + globalGroupID, con);
                r = cmd.ExecuteReader();

                while (r.Read())
                {

                    return evaluationName = r.GetValue(0).ToString();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;

        }

        private void fillEvolutionComboBox()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT Evaluation.name FROM Evaluation INNER JOIN GroupEvaluations ON Evaluation.id = GroupEvaluations.evaluationID AND GroupEvaluations.groupsID=" + globalGroupID, con);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    evaluationBox.Items.Add(r["Name"]);
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            evaluationBox.SelectedIndex = evaluationBox.Items.IndexOf(getEvaluationName());
        }

        private void fillTaskListBox()
        {
            try
            {
                int evaluatioID = getEvaluateID();
              
                con.Open();
                SqlCommand cmd = new SqlCommand("select task.name from Task inner join EvaluationTask on EvaluationTask.taskID = task.id and EvaluationTask.evaluationID=" + evaluatioID, con);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    taskList.Items.Add(r["name"]);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        private string getGroupsName()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT name FROM Groups WHERE id=" + globalGroupID, con);
                r = cmd.ExecuteReader();

                if (r.Read())
                {
                    return r.GetValue(0).ToString();
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;
        }

        private void getBloodPressure()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT B.diastolic, B.systolic, B.pressure from BloodPressure B INNER JOIN TaskUser T on T.bloodPressureID = B.id AND T.userInfoID = "+userID, con);
                r = cmd.ExecuteReader();
                if (r.Read())
                {
                    bloodPressure.Add(new BloodPressure() { diastolic = r.GetInt32(0), systolic = r.GetInt32(1), pressure = r.GetInt32(2)});
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        private void fillUserInfo()
        {
            try
            {
                int taskId = getTaskID();
                con.Open();
                SqlCommand cmd = new SqlCommand("select * from UserInfo inner join TaskUser on TaskUser.userInfoID = UserINfo.id and TaskUser.taskID='" + taskId + "' and UserInfo.name='" + (string)participantsList.SelectedItem + "'", con);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    userID = r.GetInt32(0);
                    if (!r.IsDBNull(1))
                        userInfo.Text += "Name: " + r.GetString(1);
                    if (!r.IsDBNull(2))
                        userInfo.Text += "\nSSN: " + r.GetString(2);
                    if(!r.IsDBNull(3))
                        userInfo.Text += "\nMobile: " + r.GetInt32(3);
                    if (!r.IsDBNull(4)) 
                        userInfo.Text += "\nAddress: " + r.GetString(4);
                    if (!r.IsDBNull(5))
                        if(r.GetBoolean(5))
                            userInfo.Text += "\nGender: F";
                    if (!r.IsDBNull(6))
                        if (r.GetBoolean(6))
                            userInfo.Text += "\nGender: M";
                    if (!r.IsDBNull(7))
                        userInfo.Text += "\nBirth Date:" + r.GetDateTime(7);
                    break;
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        private void fillParticipantList()
        {
            try
            {
                int taskId = getTaskID();
                con.Open();
                SqlCommand cmd = new SqlCommand("select UserInfo.name from UserInfo inner join TaskUser on TaskUser.userInfoID = UserINfo.id and TaskUser.taskID=" + taskId, con);
                r = cmd.ExecuteReader();
                while (r.Read())
                {
                    participantsList.Items.Add(r["name"]);
                    
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        private string getVideoPath()
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT R.recording from Recording R INNER JOIN TaskUser T on T.recordingid = r.id AND T.userInfoID ="+userID, con);
                r = cmd.ExecuteReader();

                if (r.Read())
                {
                    MessageBox.Show(r.GetString(0));
                    return r.GetString(0);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;
        }
        #endregion

        #region OpenForms
        public static void OpenTesterInterface()
        {
            Application.Run(new TesterInterface());
        }
        public static void OpenListOfProjects()
        {
            Application.Run(new ListOfProjects());
        }
        #endregion
    }
}
